{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import requests\n",
    "import PyPDF2\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# D:/Working/私活/保理合同/url合同模板.xlsx\n",
    "# 处理空值和None值\n",
    "excel_path = input(\"请入完整excel路径(请以'/'分割)：\")\n",
    "df = pd.read_excel(excel_path)\n",
    "df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存pdf文件\n",
    "os.makedirs('./baoli/',exist_ok=True)\n",
    "os.makedirs('./fenqi/',exist_ok=True)\n",
    "for i in df.index:\n",
    "    baoli_url = df.loc[i,'保理合同']#.split('pdf?')[0].__add__('pdf')\n",
    "    fenqi_url = df.loc[i,'分期支付协议']#.split('pdf?')[0].__add__('pdf')\n",
    "    baoli_name = df.loc[i,'保理合同'].split('.pdf?')[0].replace('/','-').split('-')[-1].__add__('.pdf')\n",
    "    fenqi_name = df.loc[i,'分期支付协议'].split('.pdf?')[0].replace('/','-').split('-')[-1].__add__('.pdf')\n",
    "    \n",
    "    loan_application_no = df.loc[i,'loan_application_no']\n",
    "    baoli_name = str(loan_application_no)+'_'+baoli_name\n",
    "    fenqi_name = str(loan_application_no)+'_'+fenqi_name\n",
    "    print(baoli_url)\n",
    "    print(fenqi_url)\n",
    "    res1 = requests.get(baoli_url)\n",
    "    res2 = requests.get(fenqi_url)\n",
    "\n",
    "    with open('./baoli/'+baoli_name,'wb') as file:\n",
    "        file.write(res1.content)\n",
    "\n",
    "    with open('./fenqi/'+fenqi_name,'wb') as file:\n",
    "        file.write(res2.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提取保理合同信息：\n",
    "from PyPDF2 import PdfFileReader\n",
    "from pathlib import Path\n",
    "baoli_path = Path('./baoli/')\n",
    "baoli_list = baoli_path.glob('*.pdf')\n",
    "\n",
    "baoli = [['贷款申请编号','保理合同编号','保理合同签署日期','基础合同/协议编号','基础合同/协议名称']] \n",
    "for i in baoli_list:\n",
    "    path = f'./baoli/{i.name}'\n",
    "    baoli_reader = PdfFileReader(path)\n",
    "    baoli_fields = baoli_reader.getFields()\n",
    "    temp = [i.stem.split('_')[0]]\n",
    "    for field in baoli_fields:\n",
    "        if field == 'contractNo':\n",
    "            contract_no = list(baoli_fields[field].values())[-1]\n",
    "            temp.append(contract_no)\n",
    "            # print(contract_no)\n",
    "        elif field == 'signDate':\n",
    "            sign_date = list(baoli_fields[field].values())[-1]\n",
    "            temp.append(sign_date)\n",
    "            # print(sign_date)\n",
    "        elif field == 'signContractNo':\n",
    "            sign_contract_no = list(baoli_fields[field].values())[-1]\n",
    "            temp.append(sign_contract_no)\n",
    "            # print(sign_contract_no)\n",
    "    temp.append('《分期支付协议》')\n",
    "    baoli.append(temp)\n",
    "print(baoli)\n",
    "\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "baoli"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提取分期协议信息\n",
    "fenqi_path = Path('./fenqi/')\n",
    "fenqi_list = fenqi_path.glob('*.pdf')\n",
    "fenqi = [['基础合同编号','商户住所',\n",
    "          '商户统一社会信用代码','商户法定代表人',\n",
    "          '底层合同期限','应收账款','商户名称','消费者姓名',\n",
    "          '服务费用',\n",
    "          '基础合同签署日期']]\n",
    "for j in fenqi_list:\n",
    "    path = f'./fenqi/{j.name}'\n",
    "    fenqi_reader = PdfFileReader(path)\n",
    "    fenqi_fields = fenqi_reader.getFields()\n",
    "    temp = []\n",
    "    date = []\n",
    "    for field in fenqi_fields:\n",
    "        if field == 'contractNo':\n",
    "            f_contract_no = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(f_contract_no)\n",
    "            # print(f_contract_no)\n",
    "        elif field == 'merchantCreditCode':\n",
    "            f_merchant_credit_code = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(f_merchant_credit_code)\n",
    "            # print(f_merchant_credit_code)\n",
    "        elif field == 'merchantAddress':\n",
    "            f_merchant_address = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(f_merchant_address)\n",
    "            # print(f_merchant_address)\n",
    "        elif field == 'legalPerson':\n",
    "            legalPerson = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(legalPerson)\n",
    "            # print(legalPerson)\n",
    "        elif field == 'merchantName':\n",
    "            merchantName = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(merchantName)\n",
    "            # print(merchantName)\n",
    "        elif field == 'userName':\n",
    "            userName = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(userName)\n",
    "            # print(userName)\n",
    "        elif field == 'money':\n",
    "            money = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(money)\n",
    "            # print(money)\n",
    "        elif field == 'periods':\n",
    "            periods = int(list(fenqi_fields[field].values())[-1])+1\n",
    "            temp.append(periods)\n",
    "            # print(periods)\n",
    "        elif field == 'serviceMoeny':\n",
    "            serviceMoeny = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(serviceMoeny)\n",
    "            # print(serviceMoeny)\n",
    "        elif field == 'yyyy':\n",
    "            year = list(fenqi_fields[field].values())[-1]\n",
    "            if year:\n",
    "                date.append(year)\n",
    "            else:\n",
    "                date.append('年为空')\n",
    "            # print(year)\n",
    "        elif field == 'mm':\n",
    "            month = list(fenqi_fields[field].values())[-1]\n",
    "            if month:   \n",
    "                date.append(month)\n",
    "            else:\n",
    "                date.append(\"月为空\")\n",
    "            # print(month)\n",
    "        elif field == 'dd':\n",
    "            day = list(fenqi_fields[field].values())[-1]\n",
    "            if day:\n",
    "                date.append(day)\n",
    "            else:\n",
    "                date.append(\"天为空\")\n",
    "    date = date[0]+'年'+date[1]+'月'+date[2]+'日'\n",
    "    temp.append(date)\n",
    "    fenqi.append(temp)\n",
    "print(fenqi)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fenqi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(baoli[1:],columns=baoli[0])\n",
    "df2 = pd.DataFrame(fenqi[1:],columns=fenqi[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.concat([df1,df2],join='outer',axis=1)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_excel(r'final.xlsx',sheet_name='总表')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#调整格式\n",
    "import xlwings as xw\n",
    "app = xw.App(visible=False,add_book=False)\n",
    "workbook = app.books.open(r'final.xlsx')\n",
    "worksheet = workbook.sheets['总表']\n",
    "worksheet.autofit()\n",
    "workbook.save()\n",
    "workbook.close()\n",
    "app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6f8ee94c255eb1f45edb80e83721093c1db1e2ea85447c0854292673b957abb8"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
